﻿Imports DTO
Imports System.Data.OleDb
Public Class PhongDAO
    Public Shared Function ThemPhong(ByVal phong As Phong) As Integer
        Dim sql As String = "insert into PHONG(TenPhong,MaLoaiPhong,GhiChu,TinhTrang) values('" + phong.TenPhong + "'," + phong.MaLoaiPhong.ToString() + ",'" + phong.GhiChu + "','" + phong.TinhTrang + "') "
        Return Dataprovider.ExecuteNonQuery(sql)

    End Function
    Public Shared Function LayDanhSach() As List(Of Phong)
        Dim ds As New List(Of Phong)
        Dim sql As String = "select * from PHONG"
        Dim rd As OleDbDataReader = Dataprovider.ExecuteReader(sql)

        While rd.Read()
            Dim p As New Phong
            p.MaPhong = rd.GetInt32(0)
            p.TenPhong = rd.GetString(1)
            p.MaLoaiPhong = rd.GetInt32(2)
            p.GhiChu = rd.GetString(3)
            p.TinhTrang = rd.GetString(4)
            ds.Add(p)
        End While
        Return ds
    End Function
    Public Shared Function CapNhatTinhTrang(ByVal maphong As Integer, ByVal tinhtrang As String) As Integer
        Dim sql As String = "update PHONG set TinhTrang='" + tinhtrang + "' where MaPhong=" + maphong.ToString()
        Return Dataprovider.ExecuteNonQuery(sql)
    End Function
    Public Shared Function TimKiem(ByVal phong As PhongCRT, ByVal loaiphong As LoaiPhongCRT) As List(Of Phong)
        Dim strtenphong As String = "1=1"
        Dim strtinhtrang As String = "1=1"
        Dim dsloaiphong As New List(Of LoaiPhong)
        Dim strmaloaiphong As New List(Of String)
        ' strmaloaiphong.Add("1-1")
        Dim strmaphong As String = "1=1"
        Dim strghichu As String = "1=1"
        If phong.CheckMaPhong = True Then
            strmaphong = "MaPhong=" + phong.MaPhong
        End If
        If phong.CheckTenPhong = True Then
            strtenphong = "TenPhong like '%" + phong.TenPhong + "%'"
        End If
        If phong.CheckMaLoaiPhong = True Or loaiphong.CheckDonGia Then

            dsloaiphong = LoaiPhongDAO.TimKiem(loaiphong)
            For i As Integer = 0 To dsloaiphong.Count - 1 Step 1
                strmaloaiphong.Add("MaLoaiPhong=" + dsloaiphong(i).MaLoaiPhong.ToString())
            Next
        End If
        If phong.CheckTinhTrang = True Then
            strtinhtrang = "TinhTrang like  '%" + phong.TinhTrang + "%'"
        End If
        If phong.CheckGhiChu = True Then

        End If
        Dim ds As New List(Of Phong)
        Dim rd As OleDbDataReader
        If dsloaiphong.Count > 0 Then
            For i As Integer = 0 To dsloaiphong.Count - 1 Step 1
                Dim sql As String = "select * from PHONG where " + strmaphong + " and " + strtenphong + " and " + strmaloaiphong(i) + " and " + strtinhtrang + " and " + strtinhtrang
                rd = Dataprovider.ExecuteReader(sql)
                While (rd.Read())
                    Dim p As New Phong
                    p.MaPhong = rd.GetInt32(0)
                    p.TenPhong = rd.GetString(1)
                    p.MaLoaiPhong = rd.GetInt32(2)
                    p.GhiChu = rd.GetString(3)
                    p.TinhTrang = rd.GetString(4)
                    ds.Add(p)
                End While
            Next
        Else
            Dim sql As String = "select * from PHONG where " + strmaphong + " and " + strtenphong + " and " + strtinhtrang + " and " + strghichu
            rd = Dataprovider.ExecuteReader(sql)
            While (rd.Read())
                Dim p As New Phong
                p.MaPhong = rd.GetInt32(0)
                p.TenPhong = rd.GetString(1)
                p.MaLoaiPhong = rd.GetInt32(2)
                p.GhiChu = rd.GetString(3)
                p.TinhTrang = rd.GetString(4)
                ds.Add(p)
            End While
        End If
        
        Return ds
    End Function
    Public Shared Function TongSoNgayThue(ByVal ngay As Date) As Integer
        Try
            Dim ngaydauthang As New Date(ngay.Year, ngay.Month, 1)
            Dim ngaycuoithang As New Date(ngay.Year, ngay.Month, 31)
            Dim sql As String = "select sum(cthd.SoNgayThue) from HOADON hd,CHITIETHOADON cthd,PHONG p where hd.MaHoaDon=cthd.MaHoaDon and cthd.MaPhong=p.MaPhong and hd.NgayThanhToan between #" + ngaydauthang + "# and #" + ngaycuoithang + "#"

            Return Long.Parse(Dataprovider.ExecuteScalar(sql))
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Shared Function SoNgayThue(ByVal maphong As Integer, ByVal ngay As Date) As Integer
        Try
            Dim ngaydauthang As New Date(ngay.Year, ngay.Month, 1)
            Dim ngaycuoithang As New Date(ngay.Year, ngay.Month, 31)
            Dim sql As String = "select sum(cthd.SoNgayThue) from HOADON hd,CHITIETHOADON cthd,PHONG p where hd.MaHoaDon=cthd.MaHoaDon and cthd.MaPhong=p.MaPhong and hd.NgayThanhToan between #" + ngaydauthang + "# and #" + ngaycuoithang + "#" + " and p.MaPhong=" + maphong.ToString()

            Return Long.Parse(Dataprovider.ExecuteScalar(sql))
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Shared Function Xoa(ByVal maphong As Integer) As Integer
        Dim sql As String = "delete from PHONG where MaPhong=" + maphong.ToString()
        Return Dataprovider.ExecuteNonQuery(sql)
    End Function
    Public Shared Function Sua(ByVal phong As Phong) As Integer
        Dim sql As String = "update Phong set TenPhong='" + phong.TenPhong.ToString() + "',MaLoaiPhong=" + phong.MaLoaiPhong.ToString() + ",GhiChu='" + phong.GhiChu + "' where MaPhong=" + phong.MaPhong.ToString()
        Return Dataprovider.ExecuteNonQuery(sql)
    End Function
End Class
